package dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collections;
import java.util.List;

import common.Commons;
import dao.MemberDao;
import dto.SelectCondition;
import entity.MemOrigin;
import entity.MemType;
import entity.Member;
import util.DBUtils;

public class MemberDaoImpl implements MemberDao {

	@Override
	public List<Member> selectByPage(int pageSize, int pg) {
		try(Connection c = DBUtils.getConnection();){
			String sql = "select member_id,member_nick,member_phone,type_name,origin_name,"
					+ " money,stat,regist_time from member left join mem_type using(type_id)"
					+ " left join mem_origin using(origin_id) limit ?,?";
			PreparedStatement ps = c.prepareStatement(sql);
			ps.setInt(1, (pg-1)*pageSize);
			ps.setInt(2, pageSize);
			ResultSet rs = ps.executeQuery();
			List<Member> mems = new ArrayList<>();
			while (rs.next()) {
				Member m = new Member();
				m.setMemberId(rs.getInt(1));
				m.setMemberNick(rs.getString(2));
				m.setMemberPhone(rs.getString(3));
				MemType type = new MemType();
				type.setTypeName(rs.getString(4));
				m.setType(type);
				MemOrigin origin = new MemOrigin();
				origin.setOriginName(rs.getString(5));
				m.setOrigin(origin);
				m.setMoney(rs.getDouble(6));
				m.setStat(rs.getInt(7));
				m.setRegistTime(rs.getTimestamp(8));
				mems.add(m);
			}
			return mems.isEmpty() ? Collections.emptyList():mems;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return Collections.emptyList();
	}

	@Override
	public Member selectById(int id) {
		try(Connection c = DBUtils.getConnection();){
			String sql = "select member_id,member_nick,member_phone,type_name,origin_name,"
					+ " money,stat,regist_time from member left join mem_type using(type_id)"
					+ " left join mem_origin using(origin_id) where member_id=?";
			PreparedStatement ps = c.prepareStatement(sql);
			ps.setInt(1, id);
			ResultSet rs = ps.executeQuery();
			if (rs.next()) {
				Member m = new Member();
				m.setMemberId(rs.getInt(1));
				m.setMemberNick(rs.getString(2));
				m.setMemberPhone(rs.getString(3));
				MemType type = new MemType();
				type.setTypeName(rs.getString(4));
				m.setType(type);
				MemOrigin origin = new MemOrigin();
				origin.setOriginName(rs.getString(5));
				m.setOrigin(origin);
				m.setMoney(rs.getDouble(6));
				m.setStat(rs.getInt(7));
				m.setRegistTime(rs.getTimestamp(8));
				return m;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}

	@Override
	public Member selectByPhone(String phone) {
		try(Connection c = DBUtils.getConnection();){
			String sql = "select member_id,member_nick,member_phone,type_name,origin_name,"
					+ " money,stat,regist_time from member left join mem_type using(type_id)"
					+ " left join mem_origin using(origin_id) where member_phone=?";
			PreparedStatement ps = c.prepareStatement(sql);
			ps.setString(1, phone);
			ResultSet rs = ps.executeQuery();
			if (rs.next()) {
				Member m = new Member();
				m.setMemberId(rs.getInt(1));
				m.setMemberNick(rs.getString(2));
				m.setMemberPhone(rs.getString(3));
				MemType type = new MemType();
				type.setTypeName(rs.getString(4));
				m.setType(type);
				MemOrigin origin = new MemOrigin();
				origin.setOriginName(rs.getString(5));
				m.setOrigin(origin);
				m.setMoney(rs.getDouble(6));
				m.setStat(rs.getInt(7));
				m.setRegistTime(rs.getTimestamp(8));
				return m;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}

	@Override
	public List<Member> selectByCondition(SelectCondition cond) {
		PreparedStatement ps = null;
		try(Connection c = DBUtils.getConnection();){
			String sql = "select member_id,member_nick,member_phone,type_name,origin_name,"
					+ " money,stat,regist_time from member left join mem_type using(type_id)"
					+ " left join mem_origin using(origin_id) where 1=1 ";
			String typeName = cond.getTypeName();
			if (typeName != null) {
				sql += " and type_name=? ";
			}
			String originName = cond.getOriginName();
			if (originName != null) {
				sql += " and origin_name=? ";
			}
			String stat = cond.getStat();
			if (stat != null) {
				sql += " and stat=? ";
			}
			sql += " limit ?,?";
			ps = c.prepareStatement(sql);
			
			int paramI = 1;
			if (typeName != null) {
				ps.setString(paramI++, typeName);
			}
			if (originName != null) {
				ps.setString(paramI++, originName);
			}
			if (stat != null) {
				int st = stat.equals("正常")? 0:1;
				ps.setInt(paramI++, st);
			}
			int pg = cond.getPage();
			ps.setInt(paramI++, (pg-1)* Commons.PAGE_SIZE);
			ps.setInt(paramI++, Commons.PAGE_SIZE);
			
			ResultSet rs = ps.executeQuery();
			List<Member> mems = new ArrayList<>();
			while (rs.next()) {
				Member m = new Member();
				m.setMemberId(rs.getInt(1));
				m.setMemberNick(rs.getString(2));
				m.setMemberPhone(rs.getString(3));
				MemType type = new MemType();
				type.setTypeName(rs.getString(4));
				m.setType(type);
				MemOrigin origin = new MemOrigin();
				origin.setOriginName(rs.getString(5));
				m.setOrigin(origin);
				m.setMoney(rs.getDouble(6));
				m.setStat(rs.getInt(7));
				m.setRegistTime(rs.getTimestamp(8));
				mems.add(m);
			}
			return mems.isEmpty() ? Collections.emptyList():mems;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return Collections.emptyList();
	}

	
	@Override
	public boolean insert(Member member) {
		try(Connection c = DBUtils.getConnection();){
			String sql = "insert into member(member_nick,member_phone,type_id,origin_id,money,stat,regist_time) "
					+ " values(?,?,1,?,0,0,now())";
			PreparedStatement ps = c.prepareStatement(sql);
			ps.setString(1, member.getMemberNick());
			ps.setString(2, member.getMemberPhone());
			ps.setInt(3, member.getOrigin().getOriginId());
			
			int row = ps.executeUpdate();
			return row == 1 ? true : false;
		
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return false;
	}

	@Override
	public boolean update(Member member) {
		try(Connection c = DBUtils.getConnection();){
			String sql = "update member set member_nick=?,member_phone=?,type_id=?,money=?,stat=? "
					+ " where member_id=? ";
			PreparedStatement ps = c.prepareStatement(sql);
			ps.setString(1, member.getMemberNick());
			ps.setString(2, member.getMemberPhone());
			ps.setInt(3, member.getType().getTypeId());
			ps.setDouble(4, member.getMoney());
			ps.setInt(5, member.getStat());
			ps.setInt(6, member.getMemberId());
			
			int row = ps.executeUpdate();
			return row == 1 ? true : false;
		
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return false;
	}

	@Override
	public boolean deleteByIds(List<Integer> ids) {
		Connection c = DBUtils.getConnection();
		try{
			c.setAutoCommit(false);
			String sql = "delete from member where member_id=? ";
			PreparedStatement ps = c.prepareStatement(sql);
			// 批处理
			for (Integer id : ids) {
				ps.setInt(1, id);
				ps.addBatch();
			}
			
			int[] row = ps.executeBatch();
			if(row.length == ids.size()) {
				c.commit();
				return true;
			}else {
				return false;
			}
		} catch (SQLException e) {
			try {
				c.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
		}finally {
			try {
				c.setAutoCommit(true);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return false;
	}

	@Override
	public int selectCount() {
		try(Connection c = DBUtils.getConnection();){
			String sql = "select count(*) from member";
			PreparedStatement ps = c.prepareStatement(sql);
			ResultSet rs = ps.executeQuery();
			if (rs.next()) {
				return rs.getInt(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return 0;
	}

	@Override
	public int selectCountByCondition(SelectCondition cond) {
		PreparedStatement ps = null;
		try(Connection c = DBUtils.getConnection();){
			String sql = "select count(*) from member left join mem_type using(type_id)"
					+ " left join mem_origin using(origin_id) where 1=1 ";
			String typeName = cond.getTypeName();
			if (typeName != null) {
				sql += " and type_name=? ";
			}
			String originName = cond.getOriginName();
			if (originName != null) {
				sql += " and origin_name=? ";
			}
			String stat = cond.getStat();
			if (stat != null) {
				sql += " and stat=? ";
			}
			ps = c.prepareStatement(sql);
			
			int paramI = 1;
			if (typeName != null) {
				ps.setString(paramI++, typeName);
			}
			if (originName != null) {
				ps.setString(paramI++, originName);
			}
			if (stat != null) {
				int st = stat.equals("正常")? 0:1;
				ps.setInt(paramI++, st);
			}
			
			ResultSet rs = ps.executeQuery();
			if(rs.next()) {
				return rs.getInt(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return 0;
	}
}
